using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.Common;
using System.Collections;
using System.Data.SqlClient;

/// <summary>
/// Summary description for LuanVan_DAO
/// </summary>
public class LuanVan_DAO:DataProvider
{
	public LuanVan_DAO()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    public ArrayList LayMang_Luanvan()
    {
        connect();
        ArrayList arr = new ArrayList();
        try
        {
            SqlCommand cmd = new SqlCommand("sp_Select_LuanVan", strCnn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                LuanVan_DTO lvdto = new LuanVan_DTO();
                lvdto.MaLuanVan = dr.GetInt32(0);
                lvdto.TenLuanVan = dr.GetString(1);
                lvdto.MaGV = dr.GetString(2);
                lvdto.DTB = dr.GetFloat(3);
                lvdto.XepLoai = dr.GetString(4);
                lvdto.MaDangKy = dr.GetInt32(5);
                lvdto.MaDot = dr.GetInt32(6);
                arr.Add(lvdto);
            }


        }
        catch (Exception es)
        {
            throw es;
        }
        return arr;
    }
    public void update(int malv, float dtb, string xeploai)
    {
        connect();
        try
        {
            SqlCommand cmd = new SqlCommand("sp_Update_LuanVan", strCnn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@malv", SqlDbType.Int);
            cmd.Parameters["@malv"].Value = malv;//@xeploai
            cmd.Parameters["@malv"].Direction = ParameterDirection.Input;

            cmd.Parameters.Add("@dtb", SqlDbType.Float);
            cmd.Parameters["@dtb"].Value = dtb;//@xeploai
            cmd.Parameters["@dtb"].Direction = ParameterDirection.Input;

            cmd.Parameters.Add("@xeploai", SqlDbType.NVarChar);
            cmd.Parameters["@xeploai"].Value = xeploai;//@xeploai
            cmd.Parameters["@xeploai"].Direction = ParameterDirection.Input;
        }
        catch (Exception e)
        {
            throw e;
        }
    }
    // Nhan mot Table cua  LuanVan
    public DataTable DSLuanVan()
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "sp_Select_LuanVan_Cot";
        // execute the stored procedure and return the results
        return GenericDataAccess.ExecuteSelectCommand(comm);
    }
    // Nhan mot Table cua  LuanVan
    public DataTable LuanVan_Select()
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "sp_LuanVan_Select_DataTable";
        // execute the stored procedure and return the results
        return GenericDataAccess.ExecuteSelectCommand(comm);
    }

    // Nhan mot Table cua  LuanVan
    public DataTable LuanVan_Select(String MaGV)
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "sp_LuanVan_Select_DataTable_MaGV";

        // create a new parameter
        DbParameter param = comm.CreateParameter();
        param.ParameterName = "@MaGV";
        param.Value = MaGV;
        param.DbType = DbType.String;

        comm.Parameters.Add(param);



        // execute the stored procedure and return the results
        return GenericDataAccess.ExecuteSelectCommand(comm);
    }

    public DataTable LuanVan_Select(String MaGV, int maDot)
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "sp_LuanVan_Select_DataTable_MaGV";

        // create a new parameter
        DbParameter param = comm.CreateParameter();
        param.ParameterName = "@MaGV";
        param.Value = MaGV;
        param.DbType = DbType.String;

        comm.Parameters.Add(param);

        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@MaDot";
        param.Value = maDot;
        param.DbType = DbType.Int32;

        comm.Parameters.Add(param);
        // execute the stored procedure and return the results
        return GenericDataAccess.ExecuteSelectCommand(comm);
    }
    // Lay chi tiet cua bang LuanVan
    public LuanVan_DTO Get_LuanVan_DTO(int MaLuanVan)
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "sp_LuanVan_Select";
        // create a new parameter
        DbParameter param = comm.CreateParameter();
        param.ParameterName = "@MaLuanVan";
        param.Value = MaLuanVan;
        param.DbType = DbType.Int32;
        
        comm.Parameters.Add(param);
        // execute the stored procedure
        DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
        // wrap retrieved data into a CategoryDetails object
        LuanVan_DTO details = new LuanVan_DTO();
        if (table.Rows.Count > 0)
        {
            details.TenLuanVan = table.Rows[0]["TenLuanVan"].ToString();
            details.MaGiaoVienPB = table.Rows[0]["MaGiaoVienPB"].ToString();


            details.NamThucHien = Int32.Parse(table.Rows[0]["NamThucHien"].ToString());
            details.DTB = float.Parse(table.Rows[0]["DTB"].ToString());

            details.XepLoai = table.Rows[0]["XepLoai"].ToString();
            details.MaDangKy = Int32.Parse(table.Rows[0]["MaDangKy"].ToString());


        }
        // return department details
        return details;
    }

    // Update bang LuanVan
    public bool LuanVan_Sua(int MaLuanVan, String TenLuanVan, String MaGiaoVienPB, int NamThucHien, float DTB, String XepLoai, int MaDangKy)
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "sp_LuanVan_Sua";
        // create a new parameter
        DbParameter param = comm.CreateParameter();
        param.ParameterName = "@MaLuanVan";
        param.Value = MaLuanVan;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@TenLuanVan";
        param.Value = TenLuanVan;
        param.DbType = DbType.String;
        param.Size = 200;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@MaGiaoVienPB";
        param.Value = MaGiaoVienPB;
        param.DbType = DbType.String;
        param.Size = 50;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@NamThucHien";
        param.Value = NamThucHien;
        param.DbType = DbType.Int32;

        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@DTB";
        param.Value = DTB;
        param.DbType = DbType.Double;

        comm.Parameters.Add(param);



        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@XepLoai";
        param.Value = XepLoai;
        param.DbType = DbType.String;

        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@MaDangKy";
        param.Value = MaDangKy;
        param.DbType = DbType.Int32;

        comm.Parameters.Add(param);
        // result will represent the number of changed rows
        int result = -1;
        try
        {
            // execute the stored procedure
            result = GenericDataAccess.ExecuteNonQuery(comm);
        }
        catch
        {
            // any errors are logged in GenericDataAccess, we ingore them here
        }
        // result will be 1 in case of success 
        return (result != -1);
    }
    // tao ra mot dong trong bang LuanVan
    public bool LuanVan_Them(int MaLuanVan, String TenLuanVan, String MaGiaoVienPB, int NamThucHien, float DTB, String XepLoai, int MaDangKy)
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "sp_LuanVan_Them";
        // create a new parameter
        DbParameter param = comm.CreateParameter();
        param.ParameterName = "@MaLuanVan";
        param.Value = MaLuanVan;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@TenLuanVan";
        param.Value = TenLuanVan;
        param.DbType = DbType.String;
        param.Size = 200;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@MaGiaoVienPB";
        param.Value = MaGiaoVienPB;
        param.DbType = DbType.String;
        param.Size = 50;
        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@NamThucHien";
        param.Value = NamThucHien;
        param.DbType = DbType.Int32;

        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@DTB";
        param.Value = DTB;
        param.DbType = DbType.Double;

        comm.Parameters.Add(param);



        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@XepLoai";
        param.Value = XepLoai;
        param.DbType = DbType.String;

        comm.Parameters.Add(param);
        // create a new parameter
        param = comm.CreateParameter();
        param.ParameterName = "@MaDangKy";
        param.Value = MaDangKy;
        param.DbType = DbType.Int32;

        comm.Parameters.Add(param);
        // result will represent the number of changed rows
        int result = -1;
        try
        {
            // execute the stored procedure
            result = GenericDataAccess.ExecuteNonQuery(comm);
        }
        catch
        {
            // any errors are logged in GenericDataAccess, we ingore them here
        }
        // result will be 1 in case of success 
        return (result != -1);
    }

    // Delete LuanVan
    public bool LuanVan_Xoa(int MaLuanVan)
    {
        // get a configured DbCommand object
        DbCommand comm = GenericDataAccess.CreateCommand();
        // set the stored procedure name
        comm.CommandText = "sp_LuanVan_Xoa";
        // create a new parameter
        DbParameter param = comm.CreateParameter();
        param.ParameterName = "@MaLuanVan";
        param.Value = MaLuanVan;
        param.DbType = DbType.Int32;
        comm.Parameters.Add(param);
        // execute the stored procedure; an error will be thrown by the
        // database in case the Category has related categories, in which case
        // it is not deleted
        int result = -1;
        try
        {
            result = GenericDataAccess.ExecuteNonQuery(comm);
        }
        catch
        {
            // any errors are logged in GenericDataAccess, we ingore them here
        }
        // result will be 1 in case of success
        return (result != -1);
    }
}
